﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using uMES.LeanManufacturing.DBUtility;
using System.Data;
using uMES.LeanManufacturing.ParameterDTO;
//using System.Data.OracleClient;
using uMESExternalControl.ToleranceInputLib;
using System.Drawing;
using System.Web.SessionState;
using System.Web;
using CamstarAPI;
using Oracle.ManagedDataAccess.Client;


namespace uMES.LeanManufacturing.ReportBusiness
{
    public class uMESResourceManagementBusiness
    {
        uMESCommonBusiness common = new uMESCommonBusiness();
        #region 设备管理
        #region 分页查询
        public uMESPagingDataDTO GetSourceData(Dictionary<string, string> para, int intPageIndex, int intPageSize)
        {
            string strSQL = GetSQL_D(para);

            uMESPagingDataDTO retR = OracleHelper.GetPagingDataIns(strSQL, intPageIndex, intPageSize);
            return retR;
        }

        protected string GetSQL_D(Dictionary<string, string> para)
        {
            StringBuilder strQuery = new StringBuilder();
            strQuery.Append(@"
                            SELECT r.resourcename,r.description,r.assetnumber,r.vendorid, r.contact,r.resourcestatusid,
                                   r.personliableid,r.notes,r.factoryid,r.teamid,r.resourceid,rs.resourcestatusname,v.vendorname,
                                   e.fullname,f.factoryname,t.teamname,e.employeename, e.employeename||'-'||e.employeeid AS nameValue
                            FROM resourcedef r
                            LEFT JOIN resourcestatus rs ON rs.resourcestatusid = r.resourcestatusid
                            LEFT JOIN vendor v ON v.vendorid = r.vendorid
                            LEFT JOIN factory f ON f.factoryid = r.factoryid
                            LEFT JOIN team t ON t.teamid = r.teamid
                            LEFT JOIN employee e ON e.employeeid = r.personliableid
                            WHERE 1 = 1 ");

            if (para.Keys.Contains("Description")) //设备名称
            {
                if (!string.IsNullOrEmpty(para["Description"]))
                {
                    strQuery.AppendLine(string.Format("AND LOWER(r.description) LIKE '%{0}%'", para["Description"].ToLower()));
                }
            }

            if (para.Keys.Contains("ResourceName")) //设备编号
            {
                if (!string.IsNullOrEmpty(para["ResourceName"]))
                {
                    strQuery.AppendLine(string.Format("AND LOWER(r.resourcename) LIKE '%{0}%'", para["ResourceName"].ToLower()));
                }
            }

            if (para.Keys.Contains("AssetNumber")) //资产编号
            {
                if (!string.IsNullOrEmpty(para["AssetNumber"]))
                {
                    strQuery.AppendLine(string.Format("AND LOWER(r.assetnumber) LIKE '%{0}%'", para["AssetNumber"].ToLower()));
                }
            }


            //if (para.Keys.Contains("StartDate")) //开始时间
            //{
            //    if (!string.IsNullOrEmpty(para["StartDate"]))
            //    {
            //        strQuery.AppendLine(string.Format("AND c.plannedstartdate >= TO_DATE('{0} 00:00:00','yyyy-MM-dd HH24:MI:SS')", para["StartDate"]));
            //    }
            //}
            //if (para.Keys.Contains("EndDate")) //结束时间
            //{
            //    if (!string.IsNullOrEmpty(para["EndDate"]))
            //    {
            //        strQuery.AppendLine(string.Format("AND c.plannedstartdate <= TO_DATE('{0} 23:59:59','yyyy-MM-dd HH24:MI:SS')", para["EndDate"]));
            //    }
            //}
            strQuery.AppendLine("ORDER BY r.resourceid DESC");

            return strQuery.ToString();
        }
        #endregion

        #region 获取分厂信息
        public DataTable GetFactoryInfo(Dictionary<string,string> para)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"
                        SELECT f.*
                        FROM factory f
                        WHERE 1 = 1
                        ");
            DataTable dt = OracleHelper.GetDataTable(sb.ToString());
            return dt;
        }
        #endregion

        #region 获取班组信息
        public DataTable GetTeamInfo(Dictionary<string, string> para)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"
                        SELECT t.*
                        FROM team t
                        WHERE 1 = 1
                      ");
            DataTable dt = OracleHelper.GetDataTable(sb.ToString());
            return dt;
        }
        #endregion

        #region 获取人员信息
        public DataTable GetEmployeeInfo(Dictionary<string, string> para)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"
                        SELECT e.employeeid,e.employeename,e.fullname,e.teamid,sv.factoryid,e.employeename||'-'||e.employeeid AS nameValue
                        FROM employee e
                        LEFT JOIN sessionvalues sv ON sv.sessionvaluesid = e.sessionvaluesid
                        LEFT JOIN Factory f ON f.factoryid = sv.factoryid
                        LEFT JOIN team t ON t.teamid = e.teamid
                        WHERE 1 = 1
                      ");
            DataTable dt = OracleHelper.GetDataTable(sb.ToString());
            return dt;
        }
        #endregion


        #region 获取设备状态信息
        public DataTable GetResourceStatusInfo()
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"
                        SELECT rs.*
                        FROM resourcestatus rs
                        WHERE 1 = 1
                      ");
            DataTable dt = OracleHelper.GetDataTable(sb.ToString());
            return dt;
        }
        #endregion

        #region 获取供应商信息
        public DataTable GetVendorInfo()
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"
                        SELECT v.*
                        FROM vendor v
                        WHERE 1 = 1
                      ");
            DataTable dt = OracleHelper.GetDataTable(sb.ToString());
            return dt;
        }
        #endregion


        #region 获取设备点检计划信息
        public DataTable GetResourceCheckPlanInfo(Dictionary<string, string> para)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"
                        SELECT r.*,f.factoryname,t.teamname,Decode(r.checkcycle,0,'周点检',1,'日点检',2,'临时点检') AS checkcycleDis,
                               e.fullname AS checkName
                        FROM resourcecheckPlanInfo r
                        LEFT JOIN Factory f ON f.factoryid = r.factoryid
                        LEFT JOIN team t ON t.teamid = r.teamid
                        LEFT JOIN employee e ON e.employeeid = r.plancheckerid
                        WHERE 1 = 1 AND r.Status ='1'
                      ");
            if (para.ContainsKey("ResourceID"))
            {
                sb.AppendLine(string.Format("AND r.ResourceID ='{0}'", para["ResourceID"]));
            }
            DataTable dt = OracleHelper.GetDataTable(sb.ToString());
            return dt;
        }
        #endregion

        #region 获取设备点检任务信息
        public DataTable GetResourceCheckTaskInfo(Dictionary<string, string> para)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"
                        SELECT r.*,f.factoryname,t.teamname,Decode(r.checkcycle,0,'周点检',1,'日点检',2,'临时点检') AS checkcycleDis,
                               e.fullname AS checkName
                        FROM ResourceCheckTaskInfo r
                        LEFT JOIN Factory f ON f.factoryid = r.factoryid
                        LEFT JOIN team t ON t.teamid = r.teamid
                        LEFT JOIN employee e ON e.employeeid = r.plancheckerid
                        WHERE 1 = 1 AND r.TaskStatus ='0'
                      ");
            if (para.ContainsKey("ResourceID"))
            {
                sb.AppendLine(string.Format("AND r.ResourceID ='{0}'", para["ResourceID"]));
            }
            DataTable dt = OracleHelper.GetDataTable(sb.ToString());
            return dt;
        }


        public uMESPagingDataDTO GetResourceTaskInfo(Dictionary<string, string> para,int intPageIndex,int intPageSize)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"
                        SELECT r.*,f.factoryname,t.teamname,Decode(r.checkcycle,0,'周点检',1,'日点检',2,'临时点检') AS checkcycleDis,
                               e.fullname AS checkName
                        FROM ResourceCheckTaskInfo r
                        LEFT JOIN Factory f ON f.factoryid = r.factoryid
                        LEFT JOIN team t ON t.teamid = r.teamid
                        LEFT JOIN employee e ON e.employeeid = r.plancheckerid
                        LEFT JOIN resourcecheckplaninfo rc ON rc.id = r.resourcecheckplanid
                        WHERE 1 = 1 AND  rc.status = 1
                      ");
            if (para.ContainsKey("ResourceID"))
            {
                sb.AppendLine(string.Format("AND r.ResourceID ='{0}'", para["ResourceID"]));
            }

            if (para.ContainsKey("TaskStatus"))
            {
                sb.AppendLine(string.Format("AND r.TaskStatus ='{0}'", para["TaskStatus"]));
            }

            if (para.ContainsKey("Description"))
            {
                sb.AppendLine(string.Format("AND Lower(r.Description)  LIKE '%{0}%'", para["Description"].ToLower()));
            }

            if (para.ContainsKey("ResourceName"))
            {
                sb.AppendLine(string.Format("AND Lower(r.ResourceName)  LIKE '%{0}%'", para["ResourceName"].ToLower()));
            }

            if (para.ContainsKey("AssetNumber"))
            {
                sb.AppendLine(string.Format("AND Lower(r.AssetNumber)  LIKE '%{0}%'", para["AssetNumber"].ToLower()));

            }

            if (para.ContainsKey("CheckName"))
            {
                sb.AppendLine(string.Format("AND Lower(e.fullname)  LIKE '%{0}%'", para["CheckName"].ToLower()));

            }

            if (para.ContainsKey("StartDate"))
            {
                sb.AppendLine(string.Format("AND r.PlanCheckDate >= TO_DATE('{0} 00:00:00','yyyy-MM-dd HH24:MI:SS')", para["StartDate"].ToLower()));

            }

            if (para.ContainsKey("EndDate"))
            {
                sb.AppendLine(string.Format("AND r.PlanCheckDate  <= TO_DATE('{0} 23:59:59','yyyy-MM-dd HH24:MI:SS')", para["EndDate"].ToLower()));

            }

            if (para.ContainsKey("PlanCheckerID"))
            {
                sb.AppendLine(string.Format("AND r.plancheckerid  = '{0}'", para["PlanCheckerID"].ToLower()));

            }

            if (para.ContainsKey("CheckCycle"))
            {
                sb.AppendLine(string.Format("AND r.checkcycle  = '{0}'", para["CheckCycle"].ToLower()));

            }

            sb.AppendLine("ORDER BY r.ResourceID, r.PlanCheckDate");
            uMESPagingDataDTO retR = OracleHelper.GetPagingDataIns(sb.ToString(), intPageIndex, intPageSize);
            return retR;
        }
        #endregion

        #region 获取设备点检附件信息
        public DataTable GetChecktaskappendixinfo(Dictionary<string, string> para)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"
                        SELECT  cti.*
                        FROM Checktaskappendixinfo cti 
                        WHERE 1 = 1
                      ");
            if (para.ContainsKey("ResourceCheckTaskID"))
            {
                sb.AppendLine(string.Format("AND cti.resourcechecktaskid ='{0}'", para["ResourceCheckTaskID"]));
            }
            DataTable dt = OracleHelper.GetDataTable(sb.ToString());
            return dt;
        }
        #endregion

        #endregion

        #region 保存数据
        public bool SaveDataToDatabase(Dictionary<string,object>para,out string strMessage)
        {
            bool bReturn = true;
            strMessage = "";
            DataSet ds = new DataSet();
            if (para.Keys.Contains("dsData"))
            {
                ds = (DataSet)para["dsData"];
            }
            try
            {
                if (ds.Tables.Count > 0)
                {
                    Dictionary<string, OracleParameter[]> SQLlist = new Dictionary<string, OracleParameter[]>();
                    for (int i= 0;i<ds.Tables.Count; i++)
                    {
                       
                        //表名称
                        string strTableName = ds.Tables[i].TableName;
                        DataTable dtMain = ds.Tables[i];
                        //主键名称
                        string strKey = ds.Tables[i].Columns[0].ColumnName;
                        //主键值
                        string[] strKeyValueList = ds.Tables[i].Rows[ds.Tables[i].Rows.Count - 1][strKey].ToString().Split('㊣');
                        string strKeyValue = strKeyValueList[0];

                        string strDelSql = "DELETE FROM " + strTableName + " m" +
                                           " WHERE " + strKey + "= :" + strKey;

                        OracleParameter[] DelParam1 = new OracleParameter[1]; 
                        DelParam1[0] = new OracleParameter();
                        DelParam1[0].ParameterName = ":" + strKey;
                        DelParam1[0].OracleDbType = OracleDbType.Int32;
                        DelParam1[0].Direction = ParameterDirection.Input;
                        DelParam1[0].Value = strKeyValue;
                
                        //'添加到哈希表
                        SQLlist.Add(strDelSql, DelParam1);

                        for (int j= 0;j< dtMain.Rows.Count;j++)
                        {
                            //表列名
                            string strMainColumn = string.Empty;
                            string strMainColumnList = string.Empty;
                            for (int h= 0;h<dtMain.Columns.Count;h++)
                            {
                                string strName = dtMain.Columns[h].ColumnName.ToString();
                                if (strMainColumn.Contains("," + strName))
                                { }
                                else
                                {
                                    strMainColumn += "," + strName;
                                    if (strName.ToLower() == strKey.ToLower())
                                    { strName += j.ToString(); }
                                    strMainColumnList += "," + ":" + strName;
                                }
                            }
                            if (strMainColumn != "")
                            {
                                strMainColumn = strMainColumn.TrimStart(',');
                                strMainColumnList = strMainColumnList.TrimStart(',');

                                string strInsertSQL1 = "INSERT INTO " + strTableName + " (" + 
                                                        strMainColumn + ")" + 
                                                        " VALUES(" + 
                                                        strMainColumnList + ")";
                                OracleParameter[] param = new OracleParameter[dtMain.Columns.Count];

                                for (int k=0;k<dtMain.Columns.Count;k++)
                                {

                                    //参数名称
                                    string strParameterName = string.Empty;
                                    strParameterName = dtMain.Columns[k].ColumnName;
                                    if (strParameterName.ToLower() == strKey.ToLower())
                                    {
                                        strParameterName += j.ToString();
                                    }

                                    strParameterName = ":" + strParameterName;
                                    //行内容
                                    string strRowContent = string.Empty;
                                    if (!string.IsNullOrEmpty(dtMain.Rows[j][k].ToString()))
                                    {
                                        strRowContent = dtMain.Rows[j][k].ToString();
                                    }


                                    //保存值
                                    string strValue = string.Empty;

                                    //类型
                                    string strType = string.Empty;
                                    if (strRowContent != "")
                                    {
                                        string[] array = strRowContent.Split('㊣');
                                        strValue = array[0];
                                        strType = array[1];
                                    }

                                    if (strType == "Date")
                                    {
                                        DateTime operDate = new DateTime();
                                        if (strValue != "")
                                        {
                                            operDate = Convert.ToDateTime(strValue);
                                        }

                                        param[k] = new OracleParameter();

                                        param[k].ParameterName =  strParameterName;
                                        param[k].OracleDbType = OracleDbType.Date;
                                        param[k].Direction = ParameterDirection.Input;
                                        param[k].Value = operDate;
                                    }

                                    else if (strType == "Integer")
                                    {
                                        param[k] = new OracleParameter();
                                        param[k].ParameterName = strParameterName;
                                        param[k].OracleDbType = OracleDbType.Int32;
                                        param[k].Direction = ParameterDirection.Input;
                                        param[k].Value = strValue;
                                    }

                                    else
                                    {
                                        param[k] = new OracleParameter();
                                        param[k].ParameterName =  strParameterName;
                                        param[k].OracleDbType = OracleDbType.Varchar2;
                                        param[k].Direction = ParameterDirection.Input;
                                        param[k].Value = strValue;
                                    }

                        

                                }
                                //添加到哈希表
                                SQLlist.Add(strInsertSQL1, param);
                            }

                        }

                    }
                    OracleHelper.ExecuteSqlTranByHash(SQLlist);
                    strMessage = "入库完成！";
                }
                else
                {
                    strMessage = "没有要保存的信息！";
                    bReturn = false;
                    return bReturn;
                }

               
            }
            catch (Exception ex)
            {
                strMessage = ex.Message;
                bReturn = false;
            }
            return bReturn;
        }

        public bool SaveDataToDatabaseNew(Dictionary<string, object> para, out string strMessage)
        {
            bool bReturn = true;
            strMessage = "";
            DataSet ds = new DataSet();
            if (para.Keys.Contains("dsData"))
            {
                ds = (DataSet)para["dsData"];
            }
            try
            {
                if (ds.Tables.Count > 0)
                {
                    Dictionary<string, OracleParameter[]> SQLlist = new Dictionary<string, OracleParameter[]>();
                    for (int i = 0; i < ds.Tables.Count; i++)
                    {

                        //表名称
                        string[] strTableNameList = ds.Tables[i].TableName.Split('㊣');
                        string strTableName = strTableNameList[0];
                        string strDelKey = strTableNameList[1];
                        DataTable dtMain = ds.Tables[i];
                        //主键名称
                        string strKey = ds.Tables[i].Columns[0].ColumnName;

                        //删除表
                        string[] strKeyValueList = ds.Tables[i].Rows[ds.Tables[i].Rows.Count - 1][strDelKey].ToString().Split('㊣');
                        string strKeyValue = strKeyValueList[0];

                        string strDelSql = "DELETE FROM " + strTableName + " m" +
                                           " WHERE " + strDelKey + "= :" + strDelKey;

                        OracleParameter[] DelParam1 = new OracleParameter[1];
                        DelParam1[0] = new OracleParameter();
                        DelParam1[0].ParameterName = ":" + strDelKey;
                        DelParam1[0].OracleDbType = OracleDbType.Varchar2;
                        DelParam1[0].Direction = ParameterDirection.Input;
                        DelParam1[0].Value = strKeyValue;

                        //'添加到哈希表
                        SQLlist.Add(strDelSql, DelParam1);

                        for (int j = 0; j < dtMain.Rows.Count-1; j++)
                        {
                            //表列名
                            string strMainColumn = string.Empty;
                            string strMainColumnList = string.Empty;
                            for (int h = 0; h < dtMain.Columns.Count; h++)
                            {
                                string strName = dtMain.Columns[h].ColumnName.ToString();
                                if (strMainColumn.Contains("," + strName))
                                { }
                                else
                                {
                                    strMainColumn += "," + strName;
                                    if (strName.ToLower() == strKey.ToLower())
                                    { strName += j.ToString(); }
                                    strMainColumnList += "," + ":" + strName;
                                }
                            }
                            if (strMainColumn != "")
                            {
                                strMainColumn = strMainColumn.TrimStart(',');
                                strMainColumnList = strMainColumnList.TrimStart(',');

                                string strInsertSQL1 = "INSERT INTO " + strTableName + " (" +
                                                        strMainColumn + ")" +
                                                        " VALUES(" +
                                                        strMainColumnList + ")";
                                OracleParameter[] param = new OracleParameter[dtMain.Columns.Count];

                                for (int k = 0; k < dtMain.Columns.Count; k++)
                                {

                                    //参数名称
                                    string strParameterName = string.Empty;
                                    strParameterName = dtMain.Columns[k].ColumnName;
                                    if (strParameterName.ToLower() == strKey.ToLower())
                                    {
                                        strParameterName += j.ToString();
                                    }

                                    strParameterName = ":" + strParameterName;
                                    //行内容
                                    string strRowContent = string.Empty;
                                    if (!string.IsNullOrEmpty(dtMain.Rows[j][k].ToString()))
                                    {
                                        strRowContent = dtMain.Rows[j][k].ToString();
                                    }


                                    //保存值
                                    string strValue = string.Empty;

                                    //类型
                                    string strType = string.Empty;
                                    if (strRowContent != "")
                                    {
                                        string[] array = strRowContent.Split('㊣');
                                        strValue = array[0];
                                        strType = array[1];
                                    }

                                    if (strType == "Date")
                                    {
                                        DateTime operDate = new DateTime();
                                        if (strValue != "")
                                        {
                                            operDate = Convert.ToDateTime(strValue);
                                        }

                                        param[k] = new OracleParameter();

                                        param[k].ParameterName = strParameterName;
                                        param[k].OracleDbType = OracleDbType.Date;
                                        param[k].Direction = ParameterDirection.Input;
                                        param[k].Value = operDate;
                                    }

                                    else if (strType == "Integer")
                                    {
                                        param[k] = new OracleParameter();
                                        param[k].ParameterName = strParameterName;
                                        param[k].OracleDbType = OracleDbType.Int32;
                                        param[k].Direction = ParameterDirection.Input;
                                        param[k].Value = strValue;
                                    }

                                    else
                                    {
                                        param[k] = new OracleParameter();
                                        param[k].ParameterName = strParameterName;
                                        param[k].OracleDbType = OracleDbType.Varchar2;
                                        param[k].Direction = ParameterDirection.Input;
                                        param[k].Value = strValue;
                                    }



                                }
                                //添加到哈希表
                                SQLlist.Add(strInsertSQL1, param);
                            }

                        }

                    }
                    OracleHelper.ExecuteSqlTranByHash(SQLlist);
                    strMessage = "保存成功！";
                }
                else
                {
                    strMessage = "没有要保存的信息！";
                    bReturn = false;
                    return bReturn;
                }


            }
            catch (Exception ex)
            {
                strMessage = ex.Message;
                bReturn = false;
            }
            return bReturn;
        }
        #endregion

        #region 更新数据
        public int UpdateDataToDatabase(DataTable dt, out string strMessage)
        {
            int iReturn = -1;
            strMessage = string.Empty;
            try
            {
                if (dt.Rows.Count > 0)
                {
                    string strTableName = dt.Rows[0][0].ToString();
                    string strKey = dt.Columns[1].ColumnName.ToString();
                    string[] strKeyValueList = dt.Rows[0][1].ToString().Split('㊣');
                    string strKeyValue = strKeyValueList[0];
                    StringBuilder strSql = new StringBuilder();
                    strSql.AppendLine("UPDATE " +strTableName);
                    strSql.AppendLine(" SET");
                    for (int i=1;i<dt.Columns.Count;i++)
                    {
                        string strColName = dt.Columns[i].ColumnName;

                        //行内容
                        string strRowContent = string.Empty;
                        if (!string.IsNullOrEmpty(dt.Rows[0][i].ToString()))
                        {
                            strRowContent = dt.Rows[0][i].ToString();
                        }


                        //保存值
                        string strValue = string.Empty;

                        //类型
                        string strType = string.Empty;
                        if (strRowContent != "")
                        {
                            string[] array = strRowContent.Split('㊣');
                            strValue = array[0];
                            strType = array[1];
                        }

                        if (strType == "Date")
                        {
                            if (i == dt.Columns.Count - 1)
                            {
                                strSql.AppendLine(string.Format(strColName + " = TO_DATE('{0}','YYYY-MM-DD HH24:MI:SS')", strValue));
                            }
                            else
                            {
                                strSql.AppendLine(string.Format(strColName + " = TO_DATE('{0}','YYYY-MM-DD HH24:MI:SS'),", strValue));
                            }
                        }
                        else
                        {
                            if (i == dt.Columns.Count - 1)
                            {
                                strSql.AppendLine(string.Format(strColName + " = '{0}'", strValue));
                            }
                            else
                            {
                                strSql.AppendLine(string.Format(strColName + " = '{0}',", strValue));
                            }
                        }
                    }

                    strSql.AppendLine(string.Format("WHERE " + strKey +" ='{0}'" ,strKeyValue));
                    iReturn = OracleHelper.ExecuteSql(strSql.ToString());
                    strMessage = "保存成功！";
                }
           
            }
            catch (Exception ex)
            {
                strMessage = ex.Message;
                iReturn = -1;
            }
            return iReturn;
        }
        #endregion

    }
}
